-- STANDARD SCRIPT
-- STEP 1 BELOW HAS TO BE CUSTOMIZED FOR EACH CLIENT 
--


--------------------------------------------------------------------------------------
-- FUNCTION TO UPDATE BATCH INSURANCE1 TABLE
-- IN PARTICULAR SELECT ACCOUNTS AVAILABLE FOR SCORING



\set ON_ERROR_STOP 1
CREATE OR REPLACE FUNCTION upd_batch_insurance0_f() RETURNS integer AS $$
DECLARE
  v_status integer;
  v_batch_run_date timestamp;
  v_cust_id integer;
BEGIN

SELECT INTO v_batch_run_date our_run_date from batch_insurance0_t limit 1;
SELECT INTO v_cust_id cust_id from batch_insurance0_t limit 1;

PERFORM ml_upd_batch_status_insurance0_new_f(v_cust_id,v_batch_run_date,1,3,'batch_insurance0_t UPDATE');



-- STEP 1 UPDATE ALL RECORDS OF batch_insurance0_t    
--    LOGIC:  which name to send to emdeon and define name_type = 'P' if the name is the patient name
--    CALCULATE: corrected_ssn 
--    CALCULATE: individual_matchkey
--    CALCULATE  THE FIELDS: medicaid_max_date AND medicare_max_date
--    APPEND THE DEFAULT THRESHOLDS;



RAISE NOTICE 'START UPDATE OF batch_insurance0_t TABLE PRIOR SCORING';

  UPDATE batch_insurance0_t
  SET
    name_type = (CASE WHEN ba.guarantor_processing = 'Y'                                                               AND  
                           g_dob IS NOT NULL                                                                           AND 
                           g_lname IS NOT NULL                                                                         AND 
                           g_fname IS NOT NULL                                                                         AND 
                           (g_ssn IS NOT NULL or (g_addr1 IS NOT NULL and g_city IS NOT NULL and g_state IS NOT NULL)) AND
                           g_dob <> dob                                                                                AND 
                           ml_ddiff_f('yy',dob,service_date_begin) <= 18    THEN 'G'
                      ELSE                                                       'P'
                      END),
    service_date_end = (CASE WHEN service_date_end IS NULL and pat_proc_type <> 'I' THEN batch_insurance0_t.service_date_begin
                             ELSE                                                        batch_insurance0_t.service_date_end
                             END),
    gender = (CASE WHEN gender = 'M' then 'M' 
                   WHEN gender = 'F' then 'F'
                   ELSE NULL
                   END),
    g_gender = (CASE WHEN g_gender = 'M' then 'M'
                     WHEN g_gender = 'F' then 'F'
                     ELSE NULL
                     END),
    state         = (CASE WHEN batch_insurance0_t.state IS NULL then '@@'
                          ELSE batch_insurance0_t.state
                          END),
    individual_matchkey = (CASE WHEN ssn IS NOT NULL             THEN ssn||dob||lname
                                ELSE                                  dob||lname||fname||batch_insurance0_t.state
                                END),
     medicaid_max_date = (CASE WHEN state = 'AZ' THEN service_date_begin + 180 - 10 
                               WHEN state = 'CO' THEN service_date_begin + 120 - 10
                               WHEN state = 'CT' THEN service_date_begin + 36500
                               WHEN state = 'DC' THEN service_date_begin + 180 - 10
                               WHEN state = 'GA' THEN service_date_begin + 180 - 10
                               WHEN state = 'MD' THEN service_date_begin + 270 - 10
                               WHEN state = 'MA' THEN service_date_begin + 90 - 10
                               WHEN state = 'NV' THEN service_date_begin + 180 - 10
                               WHEN state = 'NM' THEN service_date_begin + 120 - 10
                               WHEN state = 'PA' THEN service_date_begin + 180 - 10
                               WHEN state = 'TN' THEN service_date_begin + 180 - 10
                               WHEN state = 'TX' THEN service_date_begin + 95 - 10
                               WHEN state = 'VT' THEN service_date_begin + 180 - 10
                               ELSE                   service_date_begin + 365 - 10
                               END),
     --medicare_max_date = (CASE WHEN date_part('month',service_date_begin) <= 9 THEN ('12/31/'||(date_part('year',service_date_begin)+1))::date -10 
     --                          ELSE                                                 ('12/31/'||(date_part('year',service_date_begin)+2))::date -10
     --                          END),
     medicare_max_date = service_date_begin + 365 - 10,
     hippa_flag_com = 'A',
     hippa_flag_gov = 'A',
     prior_commercial_run   = (CASE WHEN prior_commercial_run IS NULL THEN 'N' 
                               ELSE                                        batch_insurance0_t.prior_commercial_run
                               END),
     prior_tpl_run          = (CASE WHEN prior_tpl_run IS NULL THEN 'N' 
                               ELSE                                 batch_insurance0_t.prior_tpl_run
                               END),
     prior_medicaid_run     = (CASE WHEN prior_medicaid_run IS NULL THEN 'N' 
                               ELSE                                      batch_insurance0_t.prior_medicaid_run
                               END),
     prior_medicare_run     = (CASE WHEN prior_medicare_run IS NULL THEN 'N' 
                               ELSE                                      batch_insurance0_t.prior_medicare_run
                               END),
     processing_type        = ba.processing_type,
     out_of_state           = ba.out_of_state,
     emdeon_commercial_flag = (CASE WHEN ba.emdeon_commercial_flag = 'N'                                        THEN 'A' 
                                    WHEN batch_insurance0_t.prior_commercial_run = 'Y'                          THEN 'B'
                                    ELSE batch_insurance0_t.emdeon_commercial_flag                                      
                                    END),
     emdeon_tpl_flag        = (CASE WHEN ba.emdeon_tpl_flag = 'N'                                               THEN 'A' 
                                    WHEN batch_insurance0_t.prior_tpl_run = 'Y'                                 THEN 'B'
                                    ELSE batch_insurance0_t.emdeon_tpl_flag
                                    END),
     emdeon_medicaid_flag   = (CASE WHEN ba.emdeon_medicaid_flag = 'N'                                          THEN 'A' 
                                    WHEN batch_insurance0_t.prior_medicaid_run = 'Y'                            THEN 'B'
                                    ELSE batch_insurance0_t.emdeon_medicaid_flag 
                                    END),
     emdeon_medicare_flag   = (CASE WHEN ba.emdeon_medicare_flag = 'N'                                          THEN 'A' 
                                    WHEN batch_insurance0_t.prior_medicare_run = 'Y'                            THEN 'B'
                                    ELSE batch_insurance0_t.emdeon_medicare_flag
                                    END),
     emdeon_medicare_fast = (CASE WHEN batch_insurance0_t.cust_id = 46                             THEN '0' 
                                  WHEN ba.customer_type = 'P'                                      THEN '1' 
                                  WHEN ml_ddiff_f('yr',dob,service_date_begin) > 65                THEN '1' 
                                  WHEN substring(g_employ_name from 1 for 4)  = 'DISA'             THEN '0'
                                  WHEN substring(g_employ_name from 1 for 4)  = 'DISB'             THEN '0'
                                  WHEN g_employ_name = 'DIS'                                       THEN '0'
                                  WHEN substring(g_employ_name from 1 for 6)  = 'ON DIS'           THEN '0'
                                  WHEN balance > 10000 or (balance IS NULL and charges > 10000)    THEN '0'
                                  ELSE                                                                  '1'
                                  END),
     threshold_commercial     = ba.threshold_commercial,
     threshold_tpl            = ba.threshold_tpl,
     threshold_medicaid       = ba.threshold_medicaid,
     threshold_medicare       = ba.threshold_medicare,
     threshold_bal_commercial = ba.threshold_bal_commercial,
     threshold_bal_tpl        = ba.threshold_bal_tpl,
     threshold_bal_medicaid   = ba.threshold_bal_medicaid,
     threshold_bal_medicare   = ba.threshold_bal_medicare,
     minimum_charges          = ba.minimum_charges,
     threshold_commercial_day = ba.threshold_commercial_day,
     threshold_tpl_day        = ba.threshold_tpl_day,
     threshold_medicaid_day   = ba.threshold_medicaid_day,
     threshold_medicare_day   = ba.threshold_medicare_day,
     Ithreshold_commercial_day = ba.Ithreshold_commercial_day,
     Ithreshold_tpl_day        = ba.Ithreshold_tpl_day,
     Ithreshold_medicaid_day   = ba.Ithreshold_medicaid_day,
     Ithreshold_medicare_day   = ba.Ithreshold_medicare_day


  FROM
     (SELECT * FROM insurance0_treatment_t ) ba
  WHERE
    (ba.cust_id = batch_insurance0_t.cust_id AND ba.treatment_code = batch_insurance0_t.treatment_code) 

  ;

GET DIAGNOSTICS v_status = ROW_COUNT;
RAISE NOTICE 'FINISH UPDATE OF batch_insurance0_t TABLE PRIOR SCORING: %', v_status;




-- STEP 2 CALCULATE hippa_flag_com and hippa_flag_gov IN batch_insurance0_t 
--


RAISE NOTICE 'START CALCULATION hippa_flag_com AND hippa_flag_gov IN batch_insurance0_t';


  UPDATE batch_insurance0_t
  SET
     hippa_flag_com  = (CASE WHEN ba.npi IS NULL                                                                   THEN 'B'
                             WHEN ba.flag_new = 1                                                                  THEN 'C'
                             ELSE                                                                                       '1'
                             END)
  FROM
     insurance0_npi_t ba
  WHERE
    (ba.cust_id = batch_insurance0_t.cust_id AND ba.client_npi = batch_insurance0_t.client_npi1 and ba.npi_com_ind = 1)
  ;



  UPDATE batch_insurance0_t
  SET
     hippa_flag_gov  = (CASE WHEN ba.npi IS NULL                                                                   THEN 'B' 
                             WHEN ba.flag_new = 1                                                                  THEN 'C'
                             WHEN batch_insurance0_t.state in ('OH','FL','WA','CA') AND ba.legacy IS NULL          THEN 'D'
                             WHEN batch_insurance0_t.state = 'CA' AND ba.PIN IS NULL                               THEN 'E'
                             ELSE                                                                                       '1'
                             END)
  FROM
     insurance0_npi_t ba
  WHERE
    (ba.cust_id = batch_insurance0_t.cust_id AND ba.client_npi = batch_insurance0_t.client_npi2 and ba.npi_gov_ind = 1)
  ;



RAISE NOTICE 'FINISH CALCULATION hippa_flag_com AND hippa_flag_gov IN batch_insurance0_t';





-- STEP 3 - CALCULATE THE EMDEON FLAGS FOR ALL RECORDS;
--          UPDATE GUARANTOR INFO
--          APPEND THE THRESHOLDS
--          NOTE THAT WE DO NOT HAVE ACCESS TO MEDICAID IN: AK, DE, NE, RI and UT.   VT REQUIRES A COMPLICATED APPROVAL PROCESS SO WE EXCLUDE ALSO 
--               AS OF 01/17/2011.   NH, SD, VT and MT DO NOT ALLOW MEDICAID SEARCHES WITHOUT MEDICAID NUMBER or CARD ID SO WE CANNOT SEARCH AT ALL.
--               FINALLY CT, NY, PR and WV REQUIRES ssn TO DO THE SEARCH

RAISE NOTICE 'START CALCULATING THE EMDEON FLAGS FOR ALL RECORDS';

UPDATE batch_insurance0_t
SET
emdeon_commercial_flag = (CASE WHEN emdeon_commercial_flag in ('A','B')                                                  THEN emdeon_commercial_flag 
                               WHEN hippa_flag_com in ('A','B','C')                                                      THEN 'F'
                               WHEN minimum_charges IS NOT NULL AND charges IS NOT NULL AND charges < minimum_charges    THEN 'G'
                               WHEN minimum_charges IS NOT NULL 
                                    AND 
                                    charges IS NULL 
                                    AND 
                                    balance IS NOT NULL 
                                    AND
                                    balance < minimum_charges                                                            THEN 'G'
                               WHEN processing_type = 'T' 
                                    AND 
                                    ml_ddiff_f('d',service_date_begin,our_run_date) < 45                                 THEN 'E'
                               WHEN processing_type = 'T'
                                    AND
                                    (name_type = 'P' and ssn IS NULL) OR (name_type = 'G' AND g_ssn IS NULL)             THEN 'C'
                               WHEN processing_type = 'T'                                                                THEN '1'
                               WHEN processing_type = 'H'
                                    AND
                                    (
                                      ml_ddiff_f('d',service_date_begin,our_run_date) < 45 
                                      OR 
                                      ml_ddiff_f('d',service_date_begin,our_run_date) >= 365  
                                    )                                                                                    THEN 'E'
                               WHEN processing_type = 'H'
                                    AND
                                    (name_type = 'P' and ssn IS NULL) OR (name_type = 'G' AND g_ssn IS NULL)             THEN 'C'
                               WHEN processing_type = 'H'                                                                THEN '1'
                               WHEN batch_insurance0_t.cust_id = 12  AND 
                                    ml_ddiff_f('d',service_date_begin,our_run_date) > 350                                THEN 'E'
                               WHEN batch_insurance0_t.cust_id <> 12 AND
                                    ml_ddiff_f('d',service_date_begin,our_run_date) > 170                                THEN 'E'
                               WHEN
                                (
                                 (
                                  pat_proc_type = 'I' 
                                  AND 
                                  Ithreshold_commercial_day IS NOT NULL
                                  AND
                                  ml_ddiff_f('d',service_date_begin,our_run_date) < Ithreshold_commercial_day
                                 )
                                 OR
                                 (
                                  (
                                   (pat_proc_type = 'I' AND Ithreshold_commercial_day IS NULL)
                                   OR
                                   pat_proc_type <> 'I'
                                   OR
                                   pat_proc_type IS NULL
                                  )
                                  AND
                                  (
                                   threshold_commercial_day IS NOT NULL
                                   AND 
                                   ml_ddiff_f('d',service_date_end,our_run_date) < threshold_commercial_day
                                  )
                                 )
                                )                                                                                        THEN 'H'
                               WHEN (name_type = 'P' and ssn IS NULL) OR (name_type = 'G' AND g_ssn IS NULL)             THEN 'C'
                               ELSE                                                                                           '1' 
                               END),
emdeon_tpl_flag        = (CASE WHEN emdeon_tpl_flag in ('A','B')                                                         THEN emdeon_tpl_flag 
                               WHEN hippa_flag_com in ('A','B','C')                                                      THEN 'F'
                               WHEN minimum_charges IS NOT NULL AND charges IS NOT NULL AND charges < minimum_charges    THEN 'G'
                               WHEN minimum_charges IS NOT NULL
                                    AND
                                    charges IS NULL
                                    AND
                                    balance IS NOT NULL
                                    AND
                                    balance < minimum_charges                                                            THEN 'G'
                               WHEN processing_type = 'T'
                                    AND
                                    ml_ddiff_f('d',service_date_begin,our_run_date) < 45                                 THEN 'E'
                               WHEN processing_type = 'T'
                                    AND
                                    (name_type = 'P' and ssn IS NULL) OR (name_type = 'G' AND g_ssn IS NULL)             THEN 'C'
                               WHEN processing_type = 'T'                                                                THEN '1'
                               WHEN processing_type = 'H'
                                    AND
                                    (
                                      ml_ddiff_f('d',service_date_begin,our_run_date) < 45
                                      OR
                                      ml_ddiff_f('d',service_date_begin,our_run_date) >= 365 
                                    )                                                                                    THEN 'E'
                               WHEN processing_type = 'H'
                                    AND
                                    (name_type = 'P' and ssn IS NULL) OR (name_type = 'G' AND g_ssn IS NULL)             THEN 'C'
                               WHEN processing_type = 'H'                                                                THEN '1'
                               WHEN batch_insurance0_t.cust_id = 12 AND
                                    ml_ddiff_f('d',service_date_begin,our_run_date) > 350                                THEN 'E'
                               WHEN batch_insurance0_t.cust_id <> 12 AND
                                    ml_ddiff_f('d',service_date_begin,our_run_date) > 170                                THEN 'E'
                               WHEN
                              (
                               (
                                pat_proc_type = 'I'
                                AND
                                Ithreshold_tpl_day IS NOT NULL
                                AND
                                ml_ddiff_f('d',service_date_begin,our_run_date) < Ithreshold_tpl_day
                               )
                               OR
                               (
                                (
                                 (pat_proc_type = 'I' AND Ithreshold_tpl_day IS NULL)
                                 OR
                                 pat_proc_type <> 'I'
                                 OR
                                 pat_proc_type IS NOT NULL
                                )
                                AND
                                (
                                 threshold_tpl_day IS NOT NULL
                                 AND 
                                 ml_ddiff_f('d',service_date_end,our_run_date) < threshold_tpl_day
                                )
                               )
                              )                                                                                          THEN 'H'
                               WHEN (name_type = 'P' and ssn IS NULL) OR (name_type = 'G' AND g_ssn IS NULL)             THEN 'C'
                               ELSE                                                                                           '1' 
                               END),
emdeon_medicaid_flag  = (CASE WHEN emdeon_medicaid_flag in ('A','B')                                                     THEN emdeon_medicaid_flag 
                              -- air ambulance in GA not covered for certain age group
                              WHEN batch_insurance0_t.cust_id = 46 
                                   AND 
                                   state = 'GA' 
                                   AND
                                   ml_ddiff_f('yr',dob,service_date_begin) >= 21 
                                   AND
                                   ml_ddiff_f('yr',dob,service_date_begin) <= 65                                         THEN 'A'
                              WHEN ba.medicaid_state_flag <> 1
                                   OR
                                   state in ('AK','DE','NE','RI','UT','VT','NH','SD','MT')
                                   OR
                                   ( out_of_state = 'N' AND (provider_state IS NULL OR state <> provider_state) )        THEN 'D'
                              WHEN hippa_flag_gov   <> '1'                                                               THEN 'F'
                              WHEN minimum_charges IS NOT NULL AND charges IS NOT NULL AND charges < minimum_charges     THEN 'G'
                              WHEN minimum_charges IS NOT NULL
                                    AND
                                    charges IS NULL
                                    AND
                                    balance IS NOT NULL
                                    AND
                                    balance < minimum_charges                                                            THEN 'G'
                              WHEN processing_type = 'T'
                                   AND
                                   ml_ddiff_f('d',service_date_begin,our_run_date) < 45                                  THEN 'E'
                               WHEN processing_type = 'T'
                                    AND
                                    ssn IS NULL
                                    AND
                                    state in ('CT','NY','PR','WV')                                                       THEN 'C'
                              WHEN processing_type = 'T'                                                                 THEN '1'
                              WHEN medicaid_max_date < v_batch_run_date                                                  THEN 'E'
                              WHEN processing_type = 'H'
                                    AND
                                    ml_ddiff_f('d',service_date_begin,our_run_date) < 45                                 THEN 'E'
                              WHEN processing_type = 'H'
                                    AND
                                    ssn IS NULL
                                    AND
                                    state in ('CT','NY','PR','WV')                                                       THEN 'C'
                              WHEN processing_type = 'H'                                                                 THEN '1'
                              WHEN
                              (
                               (
                                pat_proc_type = 'I'
                                AND
                                Ithreshold_medicaid_day IS NOT NULL
                                AND
                                ml_ddiff_f('d',service_date_begin,our_run_date) < Ithreshold_medicaid_day
                               )
                               OR
                               (
                                (
                                 (pat_proc_type = 'I' AND Ithreshold_medicaid_day IS NULL)
                                 OR
                                 pat_proc_type <> 'I'
                                 OR
                                 pat_proc_type IS NOT NULL
                                )
                                AND
                                (
                                 threshold_medicaid_day IS NOT NULL
                                 AND 
                                 ml_ddiff_f('d',service_date_end,our_run_date) < threshold_medicaid_day
                                )
                               )
                              )                                                                                          THEN 'H'
                              WHEN ssn IS NULL AND state in ('CT','NY','PR','WV')                                        THEN 'C'
                              ELSE                                                                                            '1' 
                              END),
emdeon_medicare_flag  = (CASE WHEN emdeon_medicare_flag in ('A','B')                                                     THEN emdeon_medicare_flag 
                              WHEN ml_ddiff_f('yy',dob,service_date_begin) <= 20                                         THEN 'E'
                              WHEN hippa_flag_gov in ('A','B','C')                                                       THEN 'F'
                              WHEN minimum_charges IS NOT NULL AND charges IS NOT NULL AND charges < minimum_charges     THEN 'G'
                              WHEN minimum_charges IS NOT NULL
                                    AND
                                    charges IS NULL
                                    AND
                                    balance IS NOT NULL
                                    AND
                                    balance < minimum_charges                                                            THEN 'G'
                              WHEN processing_type = 'T'
                                   AND
                                   ml_ddiff_f('d',service_date_begin,our_run_date) < 45                                  THEN 'E'
                              WHEN ssn IS NULL                                                                           THEN 'C'
                              WHEN processing_type = 'T'                                                                 THEN '1'
                              WHEN medicare_max_date < v_batch_run_date                                                  THEN 'E'
                              WHEN processing_type = 'H'
                                    AND
                                    ml_ddiff_f('d',service_date_begin,our_run_date) < 45                                 THEN 'E'
                              WHEN ssn IS NULL                                                                           THEN 'C'
                              WHEN processing_type = 'H'                                                                 THEN '1'
                              WHEN
                              (
                               (
                                pat_proc_type = 'I'
                                AND
                                Ithreshold_medicare_day IS NOT NULL
                                AND
                                ml_ddiff_f('d',service_date_begin,our_run_date) < Ithreshold_medicare_day
                               )
                               OR
                               (
                                (
                                 (pat_proc_type = 'I' AND Ithreshold_medicare_day IS NULL)
                                 OR
                                 pat_proc_type <> 'I'
                                 OR
                                 pat_proc_type IS NOT NULL
                                )
                                AND
                                (
                                 threshold_medicare_day IS NOT NULL
                                 AND 
                                 ml_ddiff_f('d',service_date_end,our_run_date) < threshold_medicare_day
                                )
                               )
                              )                                                                                          THEN 'H'
                              WHEN ssn IS NULL                                                                           THEN 'C'
                              ELSE                                                                                            '1' 
                              END),
calc_group            =  batch_insurance0_t.cust_id::varchar(7)||batch_insurance0_t.treatment_code

  FROM
   (SELECT
      a.cust_id,
      a.hid,
      a.acctnum,
      (case when b.state IS NOT NULL THEN 1
            else                          0 
            END) as medicaid_state_flag

    FROM
     (SELECT cust_id,
             treatment_code,
             hid,
             acctnum,
             state
      FROM batch_insurance0_t) as a

      LEFT JOIN

     (SELECT * from insurance0_medicaid_t ) as b

      ON 
     (a.cust_id = b.cust_id AND a.treatment_code = b.treatment_code AND a.state = b.state)

    ) as ba

  WHERE
    (ba.cust_id = batch_insurance0_t.cust_id ) AND (ba.hid = batch_insurance0_t.hid ) AND (ba.acctnum = batch_insurance0_t.acctnum)

  ;
  

RAISE NOTICE 'FINISH CALCULATING THE EMDEON FLAGS FOR ALL RECORDS';




------------------------
-- STEP 4 GENDER  FIELDS 
------------------------ 

  UPDATE batch_insurance0_t
  SET
     gender  = (CASE WHEN batch_insurance0_t.gender IS NOT NULL  THEN batch_insurance0_t.gender 
                     ELSE                                             ba.gender 
                     END)
  FROM
     insurance0_gender_t ba
  WHERE
    (batch_insurance0_t.fname IS NOT NULL and ba.fname = batch_insurance0_t.fname)
  ;


  UPDATE batch_insurance0_t
  SET
     g_gender  = (CASE WHEN batch_insurance0_t.g_gender IS NOT NULL  THEN batch_insurance0_t.g_gender
                     ELSE                                                 ba.gender
                     END)
  FROM
     insurance0_gender_t ba
  WHERE
    (batch_insurance0_t.g_fname IS NOT NULL and ba.fname = batch_insurance0_t.g_fname)
  ;


PERFORM ml_upd_batch_status_insurance0_new_f(v_cust_id,v_batch_run_date,1,3,'FINISH UPDATE OF batch_insurance0_t');


  return v_status;
END;
$$ LANGUAGE plpgsql;
